var express = require('express');
var router = express.Router();
const sql = require('./sql');

const { Op } = require("sequelize");
const  sequelize=require('./db');
var path = require("path");
var fs = require("fs");
var Code =require('./code')
const User = require('./model/User');
const News = require('./model/News');
const Subject = require('./model/Subject');
const JifenNotes = require('./model/JifenNotes');
const Videos = require('./model/Videos');
const Notes = require('./model/Notes');
const Likes = require('./model/Likes');

router.get('/', function (req, response) {
    let ip = req.headers['x-forwarded-for'] || // 判断是否有反向代理 IP
        req.connection.remoteAddress || // 判断 connection 的远程 IP
        req.socket.remoteAddress || // 判断后端的 socket 的 IP
        req.connection.socket.remoteAddress;
    response.send('你好啊,您的IP地址是：'+ip)
})

// 获取用户ip地址
router.get('/getip', function (req, res) {

    let ip = req.headers['x-forwarded-for'] || // 判断是否有反向代理 IP
        req.connection.remoteAddress || // 判断 connection 的远程 IP
        req.socket.remoteAddress || // 判断后端的 socket 的 IP
        req.connection.socket.remoteAddress;
    res.json({
        code: 200,
        result: ip
    })
})

//添加收藏js
router.post("/addLikes", async (req, res) => {
      let { subjectId, userId} = req.body;
     if (!userId||!subjectId) {
           res.json({
            code: 404,
            result: "subjectId, userId不能为空！"
        })
        return
     }
    await sql.connect();
    //判断是否已经收藏
     const islikes = await Likes.findOne({ 
        where:{
       subjectId:parseInt(subjectId),
         userId:parseInt(userId)
        }
       
     });
    if(islikes){
     res.json({
            code: 404,
            result: "已经收藏过了！"
        })
        return
    }
    
     Likes.create({ 
         subjectId:parseInt(subjectId),
         userId:parseInt(userId)
     }).then(result=>{
           res.json({
             code: 200,
            result
            })
     }).catch(e=>{
          res.json({
        code: 404,
        result: '收藏失败'
    })
})

   
})


//删除收藏
router.post("/deleteLikes", async (req, res) => {
      let { subjectId, userId} = req.body;
     if (!userId||!subjectId) {
           res.json({
            code: 404,
            result: "subjectId, userId不能为空！"
        })
        return
     }
    await sql.connect();
  
    const islikes = await Likes.findOne({ 
        where:{
          subjectId:parseInt(subjectId),
             userId:parseInt(userId)
        }
       
     });
  if(!islikes){
        res.json({
            code: 404,
            result: '输入id不对'
        })
  }

     const notes = await Likes.destroy({ 
              where:{
                id:islikes.id
             }
        })
         if(notes){
         res.json({
             code: 200,
             result: '删除成功！'
        })
    }else{
     res.json({
             code: 404,
             result: '删除失败'
        })
    }
})

//查询收藏
router.post("/findLikes", async (req, res) => {
         let {offset,limit,userId}=req.body;
        if (!userId) {
           res.json({
            code: 404,
            result: " userId不能为空！"
        })
        return
     }  
         
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    
      await sql.connect();
    const likes = await Likes.findAll({
        where:{
            userId
        },
        offset:offsetnum,
        limit:limitnum,
         order:[
            [ 'createdAt', 'DESC'],
         ]
    });
    
    let resArr=[];
    
    for( var i=0; i< likes.length;i++){
     const resobj = await Subject.findOne({ where: { id: likes[i].subjectId } });
     resArr.push(resobj)
    }
    
      res.json({
        code: 200,
        result: resArr
    })

})

//添加错题
router.post("/addNotes", async (req, res) => {
      let { subjectId, userId} = req.body;
     if (!userId||!subjectId) {
           res.json({
            code: 404,
            result: "subjectId, userId不能为空！"
        })
        return
     }
    await sql.connect();
    //判断是否已经
     const islikes = await Notes.findOne({ 
        where:{
      subjectId:parseInt(subjectId),
         userId:parseInt(userId)
        }
       
     });
    if(islikes){
     res.json({
            code: 404,
            result: "已经添加过了！"
        })
        return
    }
    
  Notes.create({ 
         subjectId:parseInt(subjectId),
         userId:parseInt(userId)
     }).then(result=>{
           res.json({
             code: 200,
            result: result
            })
     }).catch(e=>{
          res.json({
        code: 404,
        result: '加入错题失败'
    })
})

   
})
//删除错题
router.post("/deleteNotes", async (req, res) => {
      let { subjectId, userId} = req.body;
     if (!userId||!subjectId) {
           res.json({
            code: 404,
            result: "subjectId, userId不能为空！"
        })
        return
     }
    await sql.connect();
  
    const islikes = await Notes.findOne({ 
        where:{
          subjectId:parseInt(subjectId),
             userId:parseInt(userId)
        }
       
     });
  if(!islikes){
        res.json({
            code: 404,
            result: '输入id不对'
        })
  }

     const notes = await Notes.destroy({ 
              where:{
                id:islikes.id
             }
        })
         if(notes){
         res.json({
             code: 200,
             result: '删除成功！'
        })
    }else{
     res.json({
             code: 404,
             result: '删除失败'
        })
    }
})

//查询收藏
router.post("/findNotes", async (req, res) => {
         let {offset,limit,userId}=req.body;
        if (!userId) {
           res.json({
            code: 404,
            result: " userId不能为空！"
        })
        return
     }  
         
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    
      await sql.connect();
       const counts = await Notes.count({ where:{
            userId
        }})
    const notes = await Notes.findAll({
        where:{
            userId
        },
        offset:offsetnum,
        limit:limitnum,
         order:[
            [ 'createdAt', 'DESC'],
         ]
    });
    
    let resArr=[];
    
    for( var i=0; i< notes.length;i++){
     const resobj = await Subject.findOne({ where: { id: notes[i].subjectId } });
     resArr.push(resobj)
    }
    
      res.json({
        code: 200,
        counts,
        result: resArr
    })

})

//添加视频
router.post("/addVideos", async (req, res) => {
      let { title, url, time, imgaepath} = req.body;
     if (!title||!url||!imgaepath||!time) {
           res.json({
            code: 404,
            result: "title, url, time, imgaepath不能为空！"
        })
        return
     }
    await sql.connect();

    const videos = await Videos.create({
        title, url, time, imgaepath
    });

    res.json({
        code: 200,
        result: videos
    })
})

//查询视频
router.post("/findVideos", async (req, res) => {
      let {offset,limit}=req.body;
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    await sql.connect();
    const videos = await Videos.findAll({
          offset:offsetnum,
        limit:limitnum,
         order:[
            [ 'createdAt', 'DESC'],
         ]
    });
    res.json({
        code: 200,
        result: videos
    })
})

//模糊视频
router.post("/likeVideos", async (req, res) => {
      let { title} = req.body;
     if (!title) {
          res.json({
            code: 404,
            result: "title必须输入习题！"
        })
        return
     }
     
    await sql.connect();

    const videos = await Videos.findAll({
         where: {
        title:{ [Op.like]:'%' +title + '%' }
        }
      
    });

    res.json({
        code: 200,
        result: videos
    })
})


//删除习题
router.post("/deleteVideos", async (req, res) => {
      let {id} = req.body;
     if (!id) {
          res.json({
            code: 404,
            result: "id不能为空！"
        })
        return
     }
     
    await sql.connect();

    const videos = await Videos.destroy({
         where: {
            id
        }
      
    });
    if(videos){
         res.json({
             code: 200,
             result: '删除成功！'
        })
    }else{
     res.json({
             code: 404,
             result: '删除失败'
        })
    }
   
})


//添加习题
router.post("/addSubject", async (req, res) => {
      let { title, a, b, c,d} = req.body;
     if (!title||!a||!b) {
           res.json({
            code: 404,
            result: "title,a,b,不能为空！"
        })
        return
     }
     if(c){
          if(c.length==0){
         c=""
        }
     }
     if(d){
          if(d.length==0){
         d=""
        }
     }
     
    await sql.connect();

    const subject = await Subject.create({
        title, a, b, c,d
    });

    res.json({
        code: 200,
        result: subject
    })
})


//模糊查询习题
router.post("/likeSubject", async (req, res) => {
      let { title} = req.body;
     if (!title) {
          res.json({
            code: 404,
            result: "title必须输入习题！"
        })
        return
     }
     
    await sql.connect();

    const subject = await Subject.findAll({
         where: {
        title:{ [Op.like]:'%' +title + '%' }
        }
      
    });

    res.json({
        code: 200,
        result: subject
    })
})

 //随机查询习题条数
router.post("/randomSubject", async (req, res) => {
      let {num} = req.body;
     if (!num) {
          res.json({
            code: 404,
            result: "num不能为空！"
        })
        return
     }
  
    await sql.connect();
    const subject = await Subject.findAll({
     limit:parseInt(num) 
     ,
     order: sequelize.random()
      
    });

    res.json({
        code: 200,
        result: subject
    })
})

//查询所有习题
router.post("/findSubject", async (req, res) => {
      let {offset,limit}=req.body;
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    await sql.connect();
    const subject = await Subject.findAll({
        offset:offsetnum,
        limit:limitnum,
          order: sequelize.random()
    });
    res.json({
        code: 200,
        result: subject
    })
})

//删除习题
router.post("/deleteSubject", async (req, res) => {
      let {id} = req.body;
     if (!id) {
          res.json({
            code: 404,
            result: "id不能为空！"
        })
        return
     }
     
    await sql.connect();

    const subject = await Subject.destroy({
         where: {
            id
        }
      
    });
    if(subject){
         res.json({
             code: 200,
             result: '删除成功！'
        })
    }else{
     res.json({
             code: 404,
             result: '删除失败'
        })
    }
   
})




//添加重要新闻
router.post("/addNews", async (req, res) => {
      let { title, url, time} = req.body;
     if (!title||!url||!time) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
    await sql.connect();

    const news = await News.create({
       title, url, time
    });

    res.json({
        code: 200,
        result: news
    })
    
    
    
})
//查询重要新闻
router.post("/getNews",async(req,res)=>{
    let {offset,limit}=req.body;
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }

    const msgs = await News.findAll( {
        offset:offsetnum,
        limit:limitnum,
         order:[
            [ 'createdAt', 'DESC'],
         ]
     
    });

    res.json({
        result:{
            code:200,
            data:msgs
        }
    })
    
})


// 注册
router.post("/addUser", async (req, res) => {
    let {username, pwd, code} = req.body;
     if (!username||!pwd||!code) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
      if(!Code.findkey(code)){
           res.send({
           code:303,
              msg:"验证码错误！"
           })
             return
      }
      
      Code.deletekey(code)
    await sql.connect();

    const finduser = await User.findOne({
        where: {
            username
        }
    })
    if (finduser) {
        res.json({
            code: 404,
            msg: "该用户已经存在"
        })
        return

    }
    const user = await User.create({
        username,
         pwd,
         jifen:0
    });

    res.json({
        code: 200,
        result: user
    })
})

// 登录
router.post('/login', async (req, res)=>{
     let {
        username,
         pwd
    } = req.body;
     if (!username||!pwd) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
      const result = await User.findOne({
        where: {
        username,
         pwd
        }
    })
    if (result) {
        res.json({
            code: 200,
            msg:'登录成功！',
            result
        })
        return
    }else{
         res.json({
            code: 404,
            result: "账号或密码错误！"
        })
        return
    }
    
})

//获取注册验证码
router.post('/getcode',function(req,res){
  let userip  = req.headers['x-forwarded-for'] || // 判断是否有反向代理 IP
        req.connection.remoteAddress || // 判断 connection 的远程 IP
        req.socket.remoteAddress || // 判断后端的 socket 的 IP
        req.connection.socket.remoteAddress;
   let code=  Math.random().toString(36).substr(2, 4) // 生成4个字符串+数字随机数
   Code.insertkey(code,5*60)//5分钟

   if(Code.findkey(userip)){
      res.send({
          code:303,
          message:'操作太频繁'
      })
      return
   }else{
      Code.insertkey(userip,5)
      res.send({
          code:200,
          msg:code,
          message:'5分钟有效'
          
      })
   }


})

//查询收藏和错题本数量
router.post("/notesandLiescount", async (req, res) => {
       let {userId} = req.body;
        if (!userId) {
           res.json({
            code: 404,
            result: "userId必须输入"
        })
        return
     }
      await sql.connect();
       const notecounts = await Notes.count({ where:{  userId }})
       const likescounts = await Likes.count({ where:{  userId }}) 
      res.json({
        code: 200,
        notecounts,
        likescounts
    })

})

// 更新积分
router.post("/updatejifen", async (req, res) => {
    let { id,num,title} = req.body;
     if (!id||!num||!title) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
     
    await sql.connect();
 const finduser=  await User.findOne({where:{id}})
  var updatenum= finduser.jifen+parseInt(num)
  
   const user=  await User.update({ jifen: updatenum }, {where: { id} });
    if(user){
    // //添加积分记录表
  const  jifenNote=await  JifenNotes.create({ 
         userId:parseInt(id),
         title,
         num:parseInt(num)
         })
    if(jifenNote){
          res.json({
            code: 200,
            num:updatenum,
            result: '更新成功！'
        })
    }else{
        res.json({
        code: 404,
        result: '更新失败'
    })
}
   
    }else{
     res.json({
        code: 404,
        result: '更新失败'
    })
}
   
})




//查询积分明细
router.post("/finduserjifen", async (req, res) => {
         let {offset,limit,userId}=req.body;
        if (!userId) {
           res.json({
            code: 404,
            result: " userId不能为空！"
        })
        return
     }  
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    
      await sql.connect();
     
    const jifenNoteslist = await JifenNotes.findAll({
        where:{
            userId
        },
        offset:offsetnum,
        limit:limitnum,
         order:[
            [ 'createdAt', 'DESC'],
         ]
    });
    
      res.json({
        code: 200,
        result: jifenNoteslist
    })

})








// 更新限时模式成绩
router.post("/updatexianshiscore", async (req, res) => {
    let { id,xianshi_score,xianshi_error } = req.body;
     if (!id||!xianshi_score||!xianshi_error) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
    await sql.connect();

   const user=  await User.update({ 
       xianshi_score:xianshi_score,
       xianshi_error
       }, { where: { id} });
    
if(user){
     res.json({
        code: 200,
        result: "更新成功！"
    })
}else{
     res.json({
        code: 404,
        result: '修改失败'
    })
}
   
})


// 更新考试模式成绩
router.post("/updatekaoshiscore", async (req, res) => {
    let { id,kaoshi_score,kaoshi_time } = req.body;
     if (!id||!kaoshi_score||!kaoshi_time) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
    await sql.connect();

   const user=  await User.update({ kaoshi_score,kaoshi_time }, { where: { id} });
    
if(user){
     res.json({
        code: 200,
        result: "更新成功！"
    })
}else{
     res.json({
        code: 404,
        result: '修改失败'
    })
}
   
})


// 更新积分模式成绩
router.post("/updatejifenscore", async (req, res) => {
    let { id,jifen_score,jifen_time } = req.body;
     if (!id||!jifen_score||!jifen_time) {
           res.json({
            code: 404,
            result: "参数不齐全"
        })
        return
     }
    await sql.connect();

   const user=  await User.update({ jifen_score,jifen_time }, { where: { id} });
    
if(user){
     res.json({
        code: 200,
        result: "更新成功！"
    })
}else{
     res.json({
        code: 404,
        result: '修改失败'
    })
}
   
})


// 查询积分列表
router.post("/userjifen", async (req, res) => {
     let {offset,limit}=req.body;
    let offsetnum=null;
    let limitnum=null;
    if(offset){
     offsetnum=offset;
        offsetnum=parseInt(offsetnum);
    }
    if(limit){
        limitnum=limit;
        limitnum=parseInt(limitnum);
    }
    await sql.connect();
      const finduser = await User.findAll({
           offset:offsetnum,
            limit:limitnum,
              attributes: ['username', 'jifen'],
         order: [
         [ 'jifen', 'DESC']
        ]
      })
     res.json({
        code: 200,
        result: finduser
    })

   
})

// 查询竞技榜一列表
router.post("/getjingjiTop3", async (req, res) => {
   
    await sql.connect();
      const user1 = await User.findAll({
           limit: 3,
        attributes: ['username', 'xianshi_score','xianshi_error'],
         order: [
         [ 'xianshi_score', 'DESC'],
          ['xianshi_error'],
        ]
      })
       const user2 = await User.findAll({
             limit: 3,
        attributes: ['username', 'kaoshi_score','kaoshi_time'],
         order: [
           [ 'kaoshi_score', 'DESC'],
             ['kaoshi_time'],
        ]
      })
       const user3 = await User.findAll({
             limit: 3,
        attributes: ['username', 'jifen_score','jifen_time'],
         order: [
         [ 'jifen_score', 'DESC'],
          [ 'jifen_time']
        ]
      })
      
     res.json({
        code: 200,
        result: [user1,user2,user3]
    })

   
})

module.exports = router;